<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use think\Model;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_IOFactory;
use PHPExcel_Shared_Date;

//物料价格模块
class Matprice extends Common
{
	public function manage($page=1,$list=10){
			//接收数据
    	$request = Request::instance();

    	
    	$data=$request->post();

    	$where="1=1 ";

    	if($data){
    		if(trim($data["group_no"])){
    			$tmp=trim($data["group_no"]);
    			$where.="and g.group_no='$tmp' ";
    		}
    		if(trim($data["group_name"])){
    			$tmp=trim($data["group_name"]);
    			$where.="and g.group_name='$tmp' ";
    		}
    		if(trim($data["jxs_name"])){
    			$tmp=trim($data["jxs_name"]);
    			$where.="and cj.title='$tmp' ";
    		}
    		if(trim($data["jxs_no"])){
    			$tmp=trim($data["jxs_no"]);
    			$where.="and cj.sn='$tmp' ";
    		}

    	}else{
    		$data=["group_no"=>"","group_name"=>"","jxs_name"=>"","jxs_no"=>""];
    	}
		$sum=$applys=Db::table("pro_mat_group")->alias("g")
    	->join("mat_group_jxs j","g.id=j.gid","LEFT")
    	->join("cg_jxs cj","j.jid=cj.id","LEFT")
    	 ->join("users uc","g.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("users um","g.m_users_id=um.id","LEFT")
        ->join("employee em","um.employee_id=em.id","LEFT")
    	->group("g.id")
    	->where($where)->count();
    		$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

    	$applys=Db::table("pro_mat_group")->alias("g")
    	->join("mat_group_jxs j","g.id=j.gid","LEFT")
    	->join("cg_jxs cj","j.jid=cj.id","LEFT")
    	 ->join("users uc","g.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("users um","g.m_users_id=um.id","LEFT")
        ->join("employee em","um.employee_id=em.id","LEFT")
    	->group("g.id")
    	->where($where)->limit($start,$list)
    	->field("g.id,g.group_no,g.group_name,(select count(*) from mat_group_jxs as j1 where j1.gid=g.id) as gysnum,(select count(*) from mat_group_mats where gid=g.id) as pronum,ec.em_name as cname,g.created_on,em.em_name as mname,g.modifiled_on")
    	->select();
    	$this->assign("applys",$applys);
    	$this->assign("data",$data);
    	$this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);

		return $this->fetch();
    		

	}
	//添加分组
	public function addgroup(){
		//接收数据
    	$request = Request::instance();

    	
    	$data=$request->post();
    	if($data){
    		if(!trim($data["group_no"])){
    			return json_encode(["msg"=>"分组编号不能为空","state"=>2]);
    		}else{
    			$adddata["group_no"]=trim($data["group_no"]);
    		}
    		if(!trim($data["group_name"])){
    			return json_encode(["msg"=>"分组名不能为空","state"=>2]);
    		}else{
    			$adddata["group_name"]=trim($data["group_name"]);
    		}
    		$ischongfu=Db::table("pro_mat_group")->where("group_no='{$adddata["group_no"]}' or group_name='{$adddata["group_name"]}'")->find();
    		if($ischongfu){
    			if($ischongfu["group_no"]==$adddata["group_no"])
    				 return json_encode(["msg"=>"分组编号已存在","state"=>2]);
    			else
    				 return json_encode(["msg"=>"分组名已存在","state"=>2]);
    		}
			$adddata["remark"]=trim($data["remark"]);
			$adddata["c_users_id"]=Session::get("login_id");
			$adddata["created_on"]=date("Y-m-d H:i:s");
			try{
				$res1=Db::table("pro_mat_group")->insert($adddata);
			}catch(\Exception $e){
	        	Db::rollBack();
	            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }
	        if($res1){
				Db::commit();
	            return json_encode(["msg"=>"","state"=>1]);
	        }else{
	        	Db::rollBack();
	          return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }
    	}else{
    		 return json_encode(["msg"=>"请正确提交","state"=>2]);
    	}
	}
	//修改获取信息
	public function get_group($gid){
		$arr=Db::table("pro_mat_group")->where(["id"=>$gid])->find();
		return json_encode($arr);
	}
	//修改操作
	public function updategroup(){
		//接收数据
    	$request = Request::instance();

    	
    	$data=$request->post();
    	if($data){
    		$gid=$data["gid"];

    		if(!trim($data["group_no"])){
    			return json_encode(["msg"=>"分组编号不能为空","state"=>2]);
    		}else{
    			$adddata["group_no"]=trim($data["group_no"]);
    		}
    		if(!trim($data["group_name"])){
    			return json_encode(["msg"=>"分组名不能为空","state"=>2]);
    		}else{
    			$adddata["group_name"]=trim($data["group_name"]);
    		}
    		$ischongfu=Db::table("pro_mat_group")->where("(group_no='{$adddata["group_no"]}' or group_name='{$adddata["group_name"]}') and id<>$gid")->find();
    		if($ischongfu){
    			if($ischongfu["group_no"]==$adddata["group_no"])
    				 return json_encode(["msg"=>"分组编号已存在","state"=>2]);
    			else
    				 return json_encode(["msg"=>"分组名已存在","state"=>2]);
    		}
			$adddata["remark"]=trim($data["remark"]);
			$adddata["m_users_id"]=Session::get("login_id");
			$adddata["modifiled_on"]=date("Y-m-d H:i:s");
			$adddata["id"]=$gid;
			try{
				$res1=Db::table("pro_mat_group")->update($adddata);
			}catch(\Exception $e){
	        	Db::rollBack();
	            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }
	        if($res1){
				Db::commit();
	            return json_encode(["msg"=>"","state"=>1]);
	        }else{
	        	Db::rollBack();
	          return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }
    	}else{
    		 return json_encode(["msg"=>"请正确提交","state"=>2]);
    	}
	}
		//删除操作
	public function shanchu($gid){
		try{
				$res1=Db::table("pro_mat_group")->where("id in($gid)")->delete();
				$res2=Db::table("mat_group_mats")->where("gid in($gid)")->delete();
				$res3=Db::table("mat_group_jxs")->where("gid in($gid)")->delete();
			}catch(\Exception $e){
	        	Db::rollBack();
	            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }
	        if($res1){
				Db::commit();
	            return json_encode(["msg"=>"","state"=>1]);
	        }else{
	        	Db::rollBack();
	          return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }
	}
	//维护经销商
    public function jxslist($gid){
    	//本分组信息
    	$group=Db::table("pro_mat_group")->where(["id"=>$gid])->find();
    	$this->assign("group",$group);
    	//查询分组中的经销商
    	$jarr=Db::table("mat_group_jxs")->alias("pj")
    	->join("cg_jxs j","pj.jid=j.id")
    	->where("pj.gid=$gid and j.isdel=0")
    	->field("j.id,j.title")
    	->select();
    	//无分组
    	$nojarr=Db::table("cg_jxs")->alias("j")
    	->join("mat_group_jxs pj","j.id=pj.jid","LEFT")
    	->where("pj.gid is null and j.isdel=0")
    	->field("j.id,j.title")
    	->select();
    	$this->assign("jarr",$jarr);
    	$this->assign("nojarr",$nojarr);
    	return $this->fetch();
    }
      //增加经销商
    public function addjxs($jid,$gid){
    	$jidarr=explode(',',$jid);
    	$adddata=[];
    	for($i=0;$i<count($jidarr);$i++){
    		$adddata[]=["gid"=>$gid,"jid"=>$jidarr[$i]];
    	}
    	try{
				$res=Db::table("mat_group_jxs")->insertAll($adddata);
				
			}catch(\Exception $e){
				Db::rollBack();
	        	return 2;
	        }
	        Db::commit();
	        return 1;
    }
    //删除经销商
    public function canceljxs($jid){
    	 try{
				$res=Db::table("mat_group_jxs")->where("jid in ($jid)")->delete();
				
			}catch(\Exception $e){
				Db::rollBack();
	        	return 2;
	        }
	        Db::commit();
	        return 1;
    }
    //维护物料
	public function matlist($gid){
		$this->assign("gid",$gid);
		$apply=Db::table("pro_mat_group")->alias("g")
    	->join("mat_group_jxs j","g.id=j.gid","LEFT")
    	->group("g.id")
    	->where(["g.id"=>$gid])
    	->field("g.group_name,g.group_no,count(j.id) as gysnum,(select count(*) from mat_group_mats where gid=g.id) as pronum")
    	->find();
    	$this->assign("apply",$apply);

    	$applys=Db::table("mat_group_mats")->alias("gp")
    	->join("pro_materials p","gp.mid=p.id")
    	->where("gp.gid=$gid")
    	->field("p.id,p.unit,p.material_name,p.material_no,price,gp.start_time,gp.end_time")
    	->select();
    	$this->assign("applys",$applys);
		return $this->fetch();
	}
		   //suggest查找产品
    public function projson(){
        $data = Db::table('pro_materials')->where("is_actived=1 and is_deleted=0")->field('material_name as title,id')->select();

        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
    	//获取经销商列表
	public function getjxslist($gid){
		$arr=Db::table("mat_group_jxs")->alias("pj")
		->join("cg_jxs j","pj.jid=j.id")
		->where(["pj.gid"=>$gid])
		->field("j.id,j.title")
		->select();
		return json_encode($arr);
	}
	  //获得产品信息
    public function getpro($pid){
    	$arr = Db::table('pro_materials')->where("id=$pid")->find();
    	return json_encode($arr);
    }
    	//删除产品
	public function shanchupid($pid,$gid){
		try{
			$res=Db::table("mat_group_mats")->where("gid=$gid and mid in ($pid)")->delete();
		}catch(\Exception $e){
	        	return 2;
	        }
		return 1;
		

	}
	 //产品修改保存
    public function pro_change(){
    	$request = Request::instance();

    	
    	$data=$request->post();
    	//print_r($data);exit;
    	if($data){
    		$gid=$data["gid"];
    		$adddata=[];
    		for($i=0;$i<count($data["pid"]);$i++){
    			if(!$data["pid"][$i])
    				return json_encode(["msg"=>"请正确提交","state"=>2]);
    			if(!$data["price"][$i])
    				return json_encode(["msg"=>"价格必须填，且不能为0或负数","state"=>2]);
    			
    			if(!strtotime($data["start_time"][$i]))
    				return json_encode(["msg"=>"有效期开始时间不能为空，或格式不对","state"=>2]);
    			if(!strtotime($data["end_time"][$i]))
    				return json_encode(["msg"=>"有效期结束时间不能为空，或格式不对","state"=>2]);
    			if(strtotime($data["end_time"][$i])<=strtotime($data["start_time"][$i]))
    				return json_encode(["msg"=>"有效期结束时间必须大于开始时间，或格式不对","state"=>2]);

    			$adddata[]=["gid"=>$gid,"mid"=>$data["pid"][$i],"price"=>$data["price"][$i],"start_time"=>$data["start_time"][$i],"end_time"=>$data["end_time"][$i]];
    		}
    		Db::startTrans();
    		try{
				$res=Db::table("mat_group_mats")->where("gid=$gid")->delete();
				$res1=Db::table("mat_group_mats")->insertAll($adddata);
			}catch(\Exception $e){
				Db::rollBack();
	        	return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
	        }
	        Db::commit();
	        return json_encode(["msg"=>"","state"=>1]);
    	}else{
    		return json_encode(["msg"=>"请正确提交","state"=>2]);
    	}
    }
    //导出分组
	public function fenzuout(){
		$applys=Db::table("pro_mat_group")->alias("g")
    	->join("mat_group_jxs j","g.id=j.gid","LEFT")
    	->join("mat_group_mats p","g.id=p.gid","LEFT")
    	 ->join("users uc","g.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("users um","g.m_users_id=um.id","LEFT")
        ->join("employee em","um.employee_id=em.id","LEFT")
    	->group("g.id")
    	->field("g.id,g.group_no,g.group_name,count(j.id) as gysnum,count(p.id) as pronum,ec.em_name as cname,g.created_on,em.em_name as mname,g.modifiled_on,g.remark")
    	->select();
    	$this->assign("applys",$applys);
    	  $eid=Session::get("employee_id");
            $users=Db::table("employee")->where(["id"=>$eid])->find();
      Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("物料价格分组导出");

        #标题
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(50);
         //加粗
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setSize(14);
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFill()->getStartColor()->setARGB('00FFFF00');
         $objPHPExcel->getActiveSheet()->setCellValue('A1',"分组编号");
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('00FF0000');
        $objPHPExcel->getActiveSheet()->setCellValue('B1',"分组名称");
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('00FF0000');
        $objPHPExcel->getActiveSheet()->setCellValue('C1',"说明");
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFill()->getStartColor()->setARGB('0099CCFF');

        $objPHPExcel->getActiveSheet()->setCellValue('A2',"必填项。且必须为1-9999之间的数字");
        $objPHPExcel->getActiveSheet()->setCellValue('B2',"必填项。不能重复。不能大于50字。");
        $objPHPExcel->getActiveSheet()->setCellValue('C2',"分组说明。不能大于200字。");


        $h=2;
        for($i=0;$i<count($applys);$i++){
        	$h++;$vo=$applys[$i];
        	 $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$vo["group_no"]);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["group_name"]);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$vo["remark"]);
        }

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);


            
         $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=物料价格分组导出.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	public function daoruprice(){
		return $this->fetch();
	}
	public function daorujxs(){
		return $this->fetch();
	}
	public function daorugroup(){
		return $this->fetch();
	}
	//导如分组模版
	public function fenzuinmodel(){
		
      Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("物料分组导入模版");

        #标题
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(50);
         //加粗
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setSize(14);
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFill()->getStartColor()->setARGB('00FFFF00');
         $objPHPExcel->getActiveSheet()->setCellValue('A1',"分组编号");
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('00FF0000');
        $objPHPExcel->getActiveSheet()->setCellValue('B1',"分组名称");
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('00FF0000');
        $objPHPExcel->getActiveSheet()->setCellValue('C1',"说明");
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFill()->getStartColor()->setARGB('0099CCFF');

        $objPHPExcel->getActiveSheet()->setCellValue('A2',"必填项。且必须为1-9999之间的数字");
        $objPHPExcel->getActiveSheet()->setCellValue('B2',"必填项。不能重复。不能大于50字。");
        $objPHPExcel->getActiveSheet()->setCellValue('C2',"分组说明。不能大于200字。");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
               $objPHPExcel->getActiveSheet()->setCellValue('A3'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A4'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A5'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A6'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A7'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A8'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A9'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A10'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A11'," ");
         $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=物料分组导入模版.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	//导入分组处理
	public function new_group(){
		$file = request()->file('files');

    	if($file){
    		$info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/tmpexcel' . DS );
	    	if($info){
	            // 成功上传后 获取上传信息
	           
	            // 输出
	            $savename= $info->getSaveName();
	            $filename=ROOT_PATH . 'public/uploads/tmpexcel/'.$savename;

	            	Vendor("PHPExcel");
		        Vendor("PHPExcel.IOFactory");
		        
		       	Vendor("PHPExcel.Reader.Excel5");
		       	//$filename=ROOT_PATH . 'public/a.xls';
		       	$objReader = PHPExcel_IOFactory::createReaderForFile($filename);
				$objPHPExcel = $objReader->load($filename);
				$sheet=$objPHPExcel->setActiveSheetIndex(0);
				$highestRow = $sheet->getHighestRow(); // 取得总行数
				$highestColumm = $sheet->getHighestColumn(); // 取得总列数
				$data=[];
				$i=0;
	          	for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始
		        	if(!trim($sheet->getCell('A'.$row)->getValue())){
		        		break;
		        	}
		        	
					for ($column = 0; $column < 3; $column++) {//列数是以A列开始
						$c=chr($column+65);//echo $c;exit;
						$content=trim($sheet->getCell($c.$row)->getValue());
						switch ($column) {
							case 0:
							$data[$i]["group_no"]=$content;
								break;
							case 1:
							$data[$i]["group_name"]=$content;
								break;
							case 2:
							$data[$i]["remark"]=$content;
								break;
						}
			        }
			        $data[$i]["created_on"]=date("Y-m-d H:i:s");
			        $data[$i]["c_users_id"]=Session::get("login_id");
			        $i++;
	         	}
	         	//unlink($filename);
	         	try{
	         		$res=Db::table("pro_mat_group")->insertAll($data);
	         	}catch(\Exception $e){

	               
	                return json_encode(["msg"=>"插入失败，请检查是否存在不规范数据或编号，名称已存在","state"=>2]);
            	}
            	 return json_encode(["msg"=>"操作已成功","state"=>1]);
	        }else{
	            // 上传失败获取错误信息
	            return json_encode(["msg"=>$file->getError(),"state"=>2]);
	           
	        }
    	}else{
    		 return json_encode(["msg"=>"文件不能为空","state"=>2]);
    	}
	}
		//导出经销商excel
	public function daochujxs(){
		$applys=Db::table("pro_mat_group")->alias("g")
    	->join("mat_group_jxs j","g.id=j.gid")
    	->join("cg_jxs jx","j.jid=jx.id")->order("g.id")
    	->field("g.group_name,jx.sn")
    	->select();
		  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("经销商物料价格分组导出");

        #标题
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(50);
         //加粗
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setSize(14);
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A2:B2')->getFill()->getStartColor()->setARGB('00FFFF00');
         $objPHPExcel->getActiveSheet()->setCellValue('A1',"分组名称");
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('00FF0000');
        $objPHPExcel->getActiveSheet()->setCellValue('B1',"经销商编号");
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('00FF0000');

        $objPHPExcel->getActiveSheet()->setCellValue('A2',"必填项。必须是销路通中已存在的分组名称。");
        $objPHPExcel->getActiveSheet()->setCellValue('B2',"必填项。必须是销路通中已存在的经销商编号。");
         $h=2;
        for($i=0;$i<count($applys);$i++){
        	$h++;$vo=$applys[$i];
        	 $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$vo["group_name"]);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["sn"]);
       
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
         $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=经销商物料价格分组导出.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	//经销商分组模版
	public function jxsinmodel(){
		  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("经销商物料价格分组导入模版");

        #标题
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(50);
         //加粗
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setSize(14);
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A2:B2')->getFill()->getStartColor()->setARGB('00FFFF00');
         $objPHPExcel->getActiveSheet()->setCellValue('A1',"分组名称");
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('00FF0000');
        $objPHPExcel->getActiveSheet()->setCellValue('B1',"经销商编号");
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('00FF0000');

        $objPHPExcel->getActiveSheet()->setCellValue('A2',"必填项。必须是销路通中已存在的分组名称。");
        $objPHPExcel->getActiveSheet()->setCellValue('B2',"必填项。必须是销路通中已存在的经销商编号。");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
               $objPHPExcel->getActiveSheet()->setCellValue('A3'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A4'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A5'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A6'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A7'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A8'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A9'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A10'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A11'," ");
         $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=经销商物料价格分组导入模版.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
		//经销商导入处理
	public function new_group_jxs(){
		$file = request()->file('files');

    	if($file){
    		$info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/tmpexcel' . DS );
	    	if($info){
	            // 成功上传后 获取上传信息
	           
	            // 输出
	            $savename= $info->getSaveName();
	            $filename=ROOT_PATH . 'public/uploads/tmpexcel/'.$savename;

	            	Vendor("PHPExcel");
		        Vendor("PHPExcel.IOFactory");
		        
		       	Vendor("PHPExcel.Reader.Excel5");
		       	//$filename=ROOT_PATH . 'public/a.xls';
		       	$objReader = PHPExcel_IOFactory::createReaderForFile($filename);
				$objPHPExcel = $objReader->load($filename);
				$sheet=$objPHPExcel->setActiveSheetIndex(0);
				$highestRow = $sheet->getHighestRow(); // 取得总行数
				$highestColumm = $sheet->getHighestColumn(); // 取得总列数
				$data=[];
				$i=0;
				//查询所有组
				$groups=Db::table("pro_mat_group")->field("group_name,id")->select();
				$jxss=Db::table("cg_jxs")->where(["isdel"=>0])->field("sn,id")->select();
				$hasjxs=Db::table("mat_group_jxs")->field("jid")->select();
				$jxsarr=[];
				for($j=0;$j<count($hasjxs);$j++){
					$jxsarr[]=$hasjxs[$j]["jid"];
				}
				//return json_encode(["msg"=>"文件中存在系统中没有的分组名","state"=>2]);
	          	for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始
		        	if(!trim($sheet->getCell('A'.$row)->getValue())){
		        		break;
		        	}
		        	
					for ($column = 0; $column < 2; $column++) {//列数是以A列开始
						$c=chr($column+65);//echo $c;exit;
						$content=trim($sheet->getCell($c.$row)->getValue());
						switch ($column) {
							case 0:
								$flag=0;
								for($j=0;$j<count($groups);$j++){
									if($groups[$j]["group_name"]==$content){
										$flag=1;
										$data[$i]["gid"]=$groups[$j]["id"];
										break;
									}
								}
								if($flag==0){
									 return json_encode(["msg"=>"文件中存在系统中没有的分组名","state"=>2]);
								}
							break;
							
							case 1:
								$flag=0;
								for($j=0;$j<count($jxss);$j++){
									if($jxss[$j]["sn"]==$content){
										$flag=1;
										$data[$i]["jid"]=$jxss[$j]["id"];
										if(in_array($data[$i]["jid"],$jxsarr)){
											return json_encode(["msg"=>"存在已经分入别的小组的经销商","state"=>2]);
										}
										break;
									}
								}
								if($flag==0){
									 return json_encode(["msg"=>"文件中存在系统中没有的经销商编号","state"=>2]);
								}
							break;
							
						}
			        }
			     
			        $i++;
	         	}
	         	//unlink($filename);
	         	try{
	         		$res=Db::table("mat_group_jxs")->insertAll($data);
	         	}catch(\Exception $e){

	               
	                return json_encode(["msg"=>"插入失败，请检查是否存在不规范数据或编号，名称已存在","state"=>2]);
            	}
            	 return json_encode(["msg"=>"操作已成功","state"=>1]);
	        }else{
	            // 上传失败获取错误信息
	            return json_encode(["msg"=>$file->getError(),"state"=>2]);
	           
	        }
    	}else{
    		 return json_encode(["msg"=>"文件不能为空","state"=>2]);
    	}
	}
	
	//产品分组模版
	public function proinmodel(){
		  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("物料价格分组导入模版");

        #标题
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(50);
         //加粗
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setSize(14);
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A2:F2')->getFill()->getStartColor()->setARGB('00FFFF00');
         $objPHPExcel->getActiveSheet()->setCellValue('A1',"分组名称");
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('00FF0000');
        $objPHPExcel->getActiveSheet()->setCellValue('B1',"物料编号");
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('00FF0000');
         $objPHPExcel->getActiveSheet()->setCellValue('C1',"物料名称");
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFill()->getStartColor()->setARGB('00FF0000');
         $objPHPExcel->getActiveSheet()->setCellValue('D1',"单价（元）");
        $objPHPExcel->getActiveSheet()->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('D1')->getFill()->getStartColor()->setARGB('00FF0000');
         $objPHPExcel->getActiveSheet()->setCellValue('E1',"有效期开始时间");
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFill()->getStartColor()->setARGB('00FF0000');
         $objPHPExcel->getActiveSheet()->setCellValue('F1',"有效期结束时间");
        $objPHPExcel->getActiveSheet()->getStyle('F1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('F1')->getFill()->getStartColor()->setARGB('00FF0000');

        $objPHPExcel->getActiveSheet()->setCellValue('A2',"必填项。必须为销路通系统中已存在的分组名称。");
        $objPHPExcel->getActiveSheet()->setCellValue('B2',"必填项。必须为销路通系统中已存在的物料编号。");
        $objPHPExcel->getActiveSheet()->setCellValue('C2',"必填项。必须为销路通系统中已存在的物料名称。必须与物料编号一一对应。");
        $objPHPExcel->getActiveSheet()->setCellValue('D2',"必填项。只能填写数值类型。(支持6位小数)");
       
        $objPHPExcel->getActiveSheet()->setCellValue('E2',"必填项。只能填写日期类型。有效期开始时间不能大于有效期截止时间。格式为：年/月/日");
        $objPHPExcel->getActiveSheet()->setCellValue('F2',"必填项。只能填写日期类型。有效期开始时间不能小于有效期开始时间。格式为：年/月/日");		

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(40);
          
               $objPHPExcel->getActiveSheet()->setCellValue('A3'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A4'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A5'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A6'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A7'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A8'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A9'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A10'," ");
               $objPHPExcel->getActiveSheet()->setCellValue('A11'," ");
         $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=物料价格分组导入模版.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	//产品分组导出
	public function prodaochu(){
		$applys=Db::table("mat_group_mats")->alias("pg")
		->join("pro_materials p","pg.mid=p.id")
		->join("pro_mat_group g","g.id=pg.gid")->order("g.id")
		->field("p.material_no as pro_no,p.material_name as pro_name,g.group_name,pg.price as lingshou_price,pg.start_time,pg.end_time")
		->select();

		  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("物料价格分组导出");

        #标题
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(50);
         //加粗
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setSize(14);
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->getStyle('2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A2:F2')->getFill()->getStartColor()->setARGB('00FFFF00');
         $objPHPExcel->getActiveSheet()->setCellValue('A1',"分组名称");
       $objPHPExcel->getActiveSheet()->setCellValue('A1',"分组名称");
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('00FF0000');
        $objPHPExcel->getActiveSheet()->setCellValue('B1',"物料编号");
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('00FF0000');
         $objPHPExcel->getActiveSheet()->setCellValue('C1',"物料名称");
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFill()->getStartColor()->setARGB('00FF0000');
         $objPHPExcel->getActiveSheet()->setCellValue('D1',"单价（元）");
        $objPHPExcel->getActiveSheet()->getStyle('D1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('D1')->getFill()->getStartColor()->setARGB('00FF0000');
         $objPHPExcel->getActiveSheet()->setCellValue('E1',"有效期开始时间");
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFill()->getStartColor()->setARGB('00FF0000');
         $objPHPExcel->getActiveSheet()->setCellValue('F1',"有效期结束时间");
        $objPHPExcel->getActiveSheet()->getStyle('F1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('F1')->getFill()->getStartColor()->setARGB('00FF0000');

        $objPHPExcel->getActiveSheet()->setCellValue('A2',"必填项。必须为销路通系统中已存在的分组名称。");
        $objPHPExcel->getActiveSheet()->setCellValue('B2',"必填项。必须为销路通系统中已存在的物料编号。");
        $objPHPExcel->getActiveSheet()->setCellValue('C2',"必填项。必须为销路通系统中已存在的物料名称。必须与物料编号一一对应。");
        $objPHPExcel->getActiveSheet()->setCellValue('D2',"必填项。只能填写数值类型。(支持6位小数)");
       
        $objPHPExcel->getActiveSheet()->setCellValue('E2',"必填项。只能填写日期类型。有效期开始时间不能大于有效期截止时间。格式为：年/月/日");
        $objPHPExcel->getActiveSheet()->setCellValue('F2',"必填项。只能填写日期类型。有效期开始时间不能小于有效期开始时间。格式为：年/月/日");				
          $h=2;
        for($i=0;$i<count($applys);$i++){
        	$h++;$vo=$applys[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$vo["group_name"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["pro_no"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$vo["pro_name"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$vo["lingshou_price"]);
       		
       		$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,date("Y/m/d",strtotime($vo["start_time"])));
       		$objPHPExcel->getActiveSheet()->setCellValue('F'.$h,date("Y/m/d",strtotime($vo["end_time"])));
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(40);
         $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=物料价格分组导出.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}

	//产品导入处理
	public function new_group_pro(){
		 //return json_encode(["msg"=>"1111","state"=>2]);
		$file = request()->file('files');

    	if($file){
    		$info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/tmpexcel' . DS );
	    	if($info){
	            // 成功上传后 获取上传信息
	           
	            // 输出
	            $savename= $info->getSaveName();
	            $filename=ROOT_PATH . 'public/uploads/tmpexcel/'.$savename;
	            $groups=Db::table("pro_mat_group")->field("group_name,id")->select();

	            $pros=Db::table("pro_materials")->where("is_actived=1 and is_deleted=0")->field("id,material_no as pro_no,material_name as pro_name")->select();
	            
	            $prolist=Db::table("mat_group_mats")->field("mid as pid,gid")->select();
//return json_encode(["msg"=>count($prolist),"state"=>2]);

	            for($j=0;$j<count($groups);$j++){
	            	$groups[$j]["pids"]=[];
	            	$pidsi=0;

	            	for($k=0;$k<count($prolist);$k++){

	            		if($prolist[$k]["gid"]==$groups[$j]["id"]){

	            			$groups[$j]["pids"][$pidsi]=$prolist[$k]["pid"];

	            			$pidsi++;
	            		}
	            	}
	            }
	          
	            	Vendor("PHPExcel");
		        Vendor("PHPExcel.IOFactory");
		        
		       	Vendor("PHPExcel.Reader.Excel5");
		       	$shared = new PHPExcel_Shared_Date();
		       //	Vendor("PHPExcel.Shared.Date")
		       	//$filename=ROOT_PATH . 'public/a.xls';
		       	$objReader = PHPExcel_IOFactory::createReaderForFile($filename);
				$objPHPExcel = $objReader->load($filename);
				$sheet=$objPHPExcel->setActiveSheetIndex(0);
				$highestRow = $sheet->getHighestRow(); // 取得总行数
				$highestColumm = $sheet->getHighestColumn(); // 取得总列数
				$data=[];
				$i=0;
			
	          	for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始
		        	if(!trim($sheet->getCell('A'.$row)->getValue())){
		        		break;
		        	}
		        	$gp=[];

					for ($column = 0; $column < 6; $column++) {//列数是以A列开始
						$c=chr($column+65);//echo $c;exit;
						$content=trim($sheet->getCell($c.$row)->getValue());
												switch ($column) {
							case 0:
								$flag=0;
								for($j=0;$j<count($groups);$j++){
									if($groups[$j]["group_name"]==$content){
										$flag=1;
										$data[$i]["gid"]=$groups[$j]["id"];
										$gp=$groups[$j]["pids"];
										break;
									}
								}
								if($flag==0){
									 return json_encode(["msg"=>"文件中存在系统中没有的分组名","state"=>2]);
								}

							break;
							
							case 1:
								$flag=0;
								for($j=0;$j<count($pros);$j++){
									if($pros[$j]["pro_no"]==$content){
										$flag=1;
										$data[$i]["mid"]=$pros[$j]["id"];

										if(in_array($data[$i]["mid"],$gp)){
											return json_encode(["msg"=>"文件中有商品已经存在与该分组之中","state"=>2]);
										}
										
									}

								}
								if($flag==0){
									 return json_encode(["msg"=>"文件中存在系统中没有的产品编号","state"=>2]);
								}

							break;
							case 2:
								$flag=0;
								for($j=0;$j<count($pros);$j++){
									if($pros[$j]["pro_name"]==$content){
										$flag=1;
										if($data[$i]["mid"]!=$pros[$j]["id"]){
											return json_encode(["msg"=>"文件中有商品号与商品名不匹配","state"=>2]);
										}
									}

								}
								if($flag==0){
									 return json_encode(["msg"=>"文件中存在系统中没有的产品名","state"=>2]);
								}
							break;
							case 3:
								try{
									$data[$i]["price"]=(float)$content;
								}catch(\Exception $e){
									return json_encode(["msg"=>"文件中存在不正确的单价","state"=>2]);
								}
								if($data[$i]["price"]<=0){
									return json_encode(["msg"=>"单价必须大于0","state"=>2]);
								}
							break;
							
							
							case 4:
								try{
									$data[$i]["start_time"]=strtotime($content);
									if(!$data[$i]["start_time"]){
										$data[$i]["start_time"]=($content-25569)*(24*60*60);

									}
								}catch(\Exception $e){
									return json_encode(["msg"=>"文件中存在不正确的时间格式","state"=>2]);
								}
							break;
							case 5:
								try{
									$data[$i]["end_time"]=strtotime($content);
									if(!$data[$i]["end_time"]){
										$data[$i]["end_time"]=($content-25569)*(24*60*60);

									}
								}catch(\Exception $e){
									return json_encode(["msg"=>"文件中存在不正确的时间格式","state"=>2]);
								}
								
								if($data[$i]["start_time"]>=$data[$i]["end_time"]){
									return json_encode(["msg"=>"开始时间必须小于结束时间","state"=>2]);
								}else{
									$data[$i]["start_time"]=date("Y-m-d",$data[$i]["start_time"]);
									$data[$i]["end_time"]=date("Y-m-d",$data[$i]["end_time"]);
								}
								//return json_encode(["msg"=>$data[$i]["start_time"]."--".$data[$i]["end_time"],"state"=>2]);
							break;
						}

			        }
			     
			        $i++;
	         	}
	         	//unlink($filename);
	         	//return json_encode(["msg"=>"测试","state"=>2]);
	         	//print_r($data);exit;
	         	try{
	         		$res=Db::table("mat_group_mats")->insertAll($data);
	         	}catch(\Exception $e){

	               
	                return json_encode(["msg"=>"插入失败，请检查是否存在不规范数据或编号，名称已存在","state"=>2]);
            	}
            	 return json_encode(["msg"=>"操作已成功","state"=>1]);
	        }else{
	            // 上传失败获取错误信息
	            return json_encode(["msg"=>$file->getError(),"state"=>2]);
	           
	        }
    	}else{
    		 return json_encode(["msg"=>"文件不能为空","state"=>2]);
    	}
	}
	  //分组详情
    public function price_fz_detail($gid){
    	$this->assign("gid",$gid);
		$apply=Db::table("pro_mat_group")->alias("g")
    	->join("mat_group_jxs j","g.id=j.gid","LEFT")
    	
    	->group("g.id")
    	->where(["g.id"=>$gid])
    	->field("g.group_name,g.group_no,count(j.id) as gysnum,(select count(*) from mat_group_mats where gid=g.id) as pronum,g.remark")
    	->find();
    	$this->assign("apply",$apply);

    	$applys=Db::table("mat_group_mats")->alias("gp")
    	->join("pro_materials p","gp.mid=p.id")
    	->where("gp.gid=$gid")
    	->field("p.id,p.material_name as pro_name,p.material_no as pro_no,p.unit,gp.price as lingshou_price,gp.start_time,gp.end_time")
    	->select();
    	$this->assign("applys",$applys);

    	$jxss=Db::table("mat_group_jxs")->alias("pj")
    	->join("cg_jxs j","pj.jid=j.id")
    	->where(["pj.gid"=>$gid])
    	->field("j.title")
    	->select();
    	$jxsarr=[];
    	for($i=0;$i<count($jxss);$i++){
    		$jxsarr[]=$jxss[$i]["title"];
    	}
    	$jxsstr=implode(',',$jxsarr);
    	$this->assign("jxsstr",$jxsstr);
    	return $this->fetch();
    }
    //导出详情
   public function exceldetail($gid){

		$apply=Db::table("pro_mat_group")->alias("g")
    	->join("mat_group_jxs j","g.id=j.gid","LEFT")
    	
    	->group("g.id")
    	->where(["g.id"=>$gid])
    	->field("g.group_name,g.group_no,count(j.id) as gysnum,(select count(*) from mat_group_mats where gid=g.id) as pronum,g.remark")
    	->find();
    	

    	$applys=Db::table("mat_group_mats")->alias("gp")
    	->join("pro_materials p","gp.mid=p.id")
    	->where("gp.gid=$gid")
    	->field("p.id,p.material_name as pro_name,p.material_no as pro_no,p.unit,gp.price as lingshou_price,gp.start_time,gp.end_time")
    	->select();

    	$jxss=Db::table("mat_group_jxs")->alias("pj")
    	->join("cg_jxs j","pj.jid=j.id")
    	->where(["pj.gid"=>$gid])
    	->field("j.title")
    	->select();
    	$jxsarr=[];
    	for($i=0;$i<count($jxss);$i++){
    		$jxsarr[]=$jxss[$i]["title"];
    	}
    	$jxsstr=implode(',',$jxsarr);
    	 Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("物料价格分组详情导出");
           #标题
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(50);
         //加粗
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('1')->getFont()->setSize(14);
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
         $objPHPExcel->getActiveSheet()->setCellValue('A1',"价格分组详情");

         $objPHPExcel->getActiveSheet()->setCellValue('A2',"分组编号");
         $objPHPExcel->getActiveSheet()->setCellValue('B2',$apply["group_no"]);
         $objPHPExcel->getActiveSheet()->setCellValue('C2',"分组名称");
         $objPHPExcel->getActiveSheet()->setCellValue('D2',$apply["group_name"]);
         $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true);
         $objPHPExcel->getActiveSheet()->setCellValue('E2',"包含经销商数");
         $objPHPExcel->getActiveSheet()->setCellValue('F2',$apply["gysnum"]);
         $objPHPExcel->getActiveSheet()->setCellValue('G2',"包含单品数");
         $objPHPExcel->getActiveSheet()->setCellValue('H2',$apply["pronum"]);

         $objPHPExcel->getActiveSheet()->setCellValue('A3',"备注");
         $objPHPExcel->getActiveSheet()->setCellValue('B3',$apply["remark"]);
         $objPHPExcel->getActiveSheet()->mergeCells('B3:H3');
         $objPHPExcel->getActiveSheet()->setCellValue('A4',"包含经销商");
         $objPHPExcel->getActiveSheet()->setCellValue('B4',$jxsstr);
         $objPHPExcel->getActiveSheet()->mergeCells('B4:H4');
         $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(50);

         $objPHPExcel->getActiveSheet()->getStyle('A5:F5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A5:F5')->getFill()->getStartColor()->setARGB('0099CCFF');
         //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->setCellValue('A5',"产品编码");
        $objPHPExcel->getActiveSheet()->setCellValue('B5',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('C5',"单位");
        $objPHPExcel->getActiveSheet()->setCellValue('D5',"单价(元)");
        $objPHPExcel->getActiveSheet()->setCellValue('E5',"有效从");
        $objPHPExcel->getActiveSheet()->setCellValue('F5',"有效到");
        $h=5;
        for($i=0;$i<count($applys);$i++){
        	$h++;$vo=$applys[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$vo["pro_no"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["pro_name"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$vo["unit"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$vo["lingshou_price"]);
        	
        	$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$vo["start_time"]);
        	$objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$vo["end_time"]);
        }

         $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
             $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
             $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
             $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);


    	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=物料价格分组详情导出.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
   }
}